<?php
namespace app\admin\controller;
use app\admin\model\Stock;
use app\admin\model\Stocktotal;
use think\console\Table;
use think\Db;
use think\Request;
use think\Controller;
use think\facade\Env;
class UpFiles extends Common
{
    private function saveExcel(){
        $objPHPExcel = new \PHPExcel();
// Set document properties
        echo date('H:i:s') , " Set document properties" , PHP_EOL;
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("PHPExcel Test Document")
            ->setSubject("PHPExcel Test Document")
            ->setDescription("Test document for PHPExcel, generated using PHP classes.")
            ->setKeywords("office PHPExcel php")
            ->setCategory("Test result file");


// Add some data
        echo date('H:i:s') , " Add some data" , PHP_EOL;
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'Hello')
            ->setCellValue('B2', 'world!')
            ->setCellValue('C1', 'Hello')
            ->setCellValue('D2', 'world!');

// Miscellaneous glyphs, UTF-8
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A4', 'Miscellaneous glyphs')
            ->setCellValue('A5', 'éàèùâêîôûëïüÿäöüç');


        $objPHPExcel->getActiveSheet()->setCellValue('A8',"Hello\nWorld");
        $objPHPExcel->getActiveSheet()->getRowDimension(8)->setRowHeight(-1);
        $objPHPExcel->getActiveSheet()->getStyle('A8')->getAlignment()->setWrapText(true);


        $value = "-ValueA\n-Value B\n-Value C";
        $objPHPExcel->getActiveSheet()->setCellValue('A10', $value);
        $objPHPExcel->getActiveSheet()->getRowDimension(10)->setRowHeight(-1);
        $objPHPExcel->getActiveSheet()->getStyle('A10')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('A10')->setQuotePrefix(true);



// Rename worksheet
        echo date('H:i:s') , " Rename worksheet" , PHP_EOL;
        $objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file
        echo date('H:i:s') , " Write to Excel2007 format" , PHP_EOL;
        $callStartTime = microtime(true);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save(str_replace('.php', '.xlsx', __FILE__));
        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

//        echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)) , PHP_EOL;
        echo date('H:i:s') , " File written to " , str_replace('.php', '.xlsx', './') , PHP_EOL;
        echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , PHP_EOL;
// Echo memory usage
        echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , PHP_EOL;


// Save Excel 95 file
        echo date('H:i:s') , " Write to Excel5 format" , PHP_EOL;
        $callStartTime = microtime(true);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save(str_replace('.php', '.xls', __FILE__));
        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date('H:i:s') , " File written to " , str_replace('.php', '.xls', pathinfo(__FILE__, PATHINFO_BASENAME)) , PHP_EOL;
        echo 'Call time to write Workbook was ' , sprintf('%.4f',$callTime) , " seconds" , PHP_EOL;
// Echo memory usage
        echo date('H:i:s') , ' Current memory usage: ' , (memory_get_usage(true) / 1024 / 1024) , " MB" , PHP_EOL;


// Echo memory peak usage
        echo date('H:i:s') , " Peak memory usage: " , (memory_get_peak_usage(true) / 1024 / 1024) , " MB" , PHP_EOL;

// Echo done
        echo date('H:i:s') , " Done writing files" , PHP_EOL;
        echo 'Files have been created in ' , getcwd() , PHP_EOL;
    }
    private function insertSql($file){
        $objPHPExcel = \PHPExcel_IOFactory::load($file);
        $sheet = $objPHPExcel->getSheet(0);        // 读取第一个工作表(编号从 0 开始)
        $highestRow = $sheet->getHighestRow();           // 取得总行数
        $highestColumn = $sheet->getHighestColumn();     // 取得总列数
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        $pos = array_search($highestColumn,$arr);
//        $field_arr=[];
//        for ($column = 0; $column <= $pos; $column++) {
//            $field_arr[] = $sheet->getCellByColumnAndRow($column, 1)->getValue();
//        }
//        return ['field_arr'=>$field_arr];
        try{
            Db::query('drop table stock');
        }catch (\Exception $e){}
        $sql = "CREATE TABLE stock (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        stock_id CHAR(6) NOT NULL,
        stock_name VARCHAR(30) NOT NULL,
        rating CHAR(1) NOT NULL
        ) DEFAULT CHARSET=utf8";
        Db::query($sql);
        // 一次读取一列
        $res_arr = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            $row_arr = array();
            for ($column = 0; $column <= $pos; $column++) {
                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                $row_arr[] =$val;
            }
            $row_arr = [
                'stock_id'=>$row_arr[0],
                'stock_name'=>$row_arr[1],
                'rating'=>$row_arr[2],
            ];
            $res_arr[] = $row_arr;
        }
        $stockModel = new Stock();
        $res = $stockModel->saveAll($res_arr);
        return ['res'=>$res];
    }
    private function insertSqlTotal($file){
        $objPHPExcel = \PHPExcel_IOFactory::load($file);
        $sheet = $objPHPExcel->getSheet(0);        // 读取第一个工作表(编号从 0 开始)
        $highestRow = $sheet->getHighestRow();           // 取得总行数
        $highestColumn = $sheet->getHighestColumn();     // 取得总列数
        $arr = array('A','B','C','D','E','F','G','H','I','J','K','L','M', 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
        $pos = array_search($highestColumn,$arr);
        try{
            Db::query('drop table stock_total');
        }catch (\Exception $e){}
        $sql = "CREATE TABLE stock_total (
        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        stock_id CHAR(6) NOT NULL,
        stock_name VARCHAR(30) NOT NULL,
        Report_year DATETIME NOT NULL,
        ROE decimal(10,2) default 0 ,
        Net_profit bigint default 0,
        profit_margin decimal(10,2) default 0,
        net_asset_per decimal(10,2) default 0,
        core_growth decimal(10,2) default 0,
        NP_growth decimal(10,2) default 0
        ) DEFAULT CHARSET=utf8";
        Db::query($sql);
//        return ['a'=>time()];
        $res_arr = array();
        for ($row = 2; $row <= $highestRow; $row++) {
            $row_arr = array();
            for ($column = 0; $column <= $pos; $column++) {
                $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                $row_arr[] =$val;
            }
            $row_arr = [
                'stock_id'=>$row_arr[0],
                'stock_name'=>$row_arr[1],
                'Report_year'=>date('Y-m-d H:i:s',\PHPExcel_Shared_Date::ExcelToPHP($row_arr[2])),
                'ROE'=>$row_arr[3],
                'Net_profit'=>$row_arr[4],
                'profit_margin'=>$row_arr[5],
                'net_asset_per'=>$row_arr[6],
                'core_growth'=>$row_arr[7],
                'NP_growth'=>$row_arr[8]
            ];
            $res_arr[] = $row_arr;
        }
        $stockModel = new Stocktotal();
        $res = $stockModel->saveAll($res_arr);
        return ['res'=>$res];
    }
    public function importSqlFromTest(){
        // 获取上传文件表单字段名
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件
        $file = request()->file($fileKey['0']);
        $info = $file->validate(['ext' => 'xlsx'])->move('uploads');
        if($info){
            $result['code'] = 1;
            $result['info'] = 'excel文件上传成功!';
            $path=str_replace('\\','/',$info->getSaveName());
            $url = '/home/html/ceshi/phptest/public/uploads/'. $path;
            $result['url'] = '/uploads/'. $path;
            $this->insertSql($url);
            return $result;
        }else{
            // 上传失败获取错误信息
            $result['code'] =0;
            $result['info'] =  $file->getError();
            $result['url'] = '';
            return $result;
        }
    }
    public function importSqlFromExcel(){
        // 获取上传文件表单字段名
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件
        $file = request()->file($fileKey['0']);
        $info = $file->validate(['ext' => 'xlsx'])->move('uploads');
        if($info){
            $result['code'] = 1;
            $result['info'] = 'excel文件上传成功!';
            $path=str_replace('\\','/',$info->getSaveName());
            $path = '20200819/323a5924b6280dcb0a2428257661610e.xlsx';
            $url = '/home/html/ceshi/phptest/public/uploads/'. $path;
            $result['url'] = '/uploads/'. $path;
            $this->insertSqlTotal($url);
            return $result;
        }else{
            // 上传失败获取错误信息
            $result['code'] =0;
            $result['info'] =  $file->getError();
            $result['url'] = '';
            return $result;
        }
    }
    public function upload(){
        // 获取上传文件表单字段名
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件
        $file = request()->file($fileKey['0']);
        // 移动到框架应用根目录/public/uploads/ 目录下

        $info = $file->validate(['ext' => 'jpg,png,gif,jpeg'])->move('uploads');
        if($info){
            $result['code'] = 1;
            $result['info'] = '图片上传成功!';
            $path=str_replace('\\','/',$info->getSaveName());
            $result['url'] = '/uploads/'. $path;
            return $result;
        }else{
            // 上传失败获取错误信息

            $result['code'] =0;
            $result['info'] =  $file->getError();
            $result['url'] = '';
            return $result;
        }
    }
    public function file(){
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件 例如上传了001.jpg
        $file = request()->file($fileKey['0']);
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->validate(['ext' => 'zip,rar,pdf,swf,ppt,psd,ttf,txt,xls,doc,docx'])->move('uploads');
        if($info){
            $result['code'] = 0;
            $result['info'] = '文件上传成功!';
            $path=str_replace('\\','/',$info->getSaveName());

            $result['url'] = '/uploads/'. $path;
            $result['ext'] = $info->getExtension();
            $result['size'] = byte_format($info->getSize(),2);
            return $result;
        }else{
            // 上传失败获取错误信息
            $result['code'] =1;
            $result['info'] = '文件上传失败!';
            $result['url'] = '';
            return $result;
        }
    }
    public function pic(){
        // 获取上传文件表单字段名
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件
        $file = request()->file($fileKey['0']);
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->validate(['ext' => 'jpg,png,gif,jpeg'])->move(Env::get('root_path') . 'public/uploads');
        if($info){
            $result['code'] = 1;
            $result['info'] = '图片上传成功!';
            $path=str_replace('\\','/',$info->getSaveName());
            $result['url'] = '/uploads/'. $path;
            return json_encode($result,true);
        }else{
            // 上传失败获取错误信息
            $result['code'] =0;
            $result['info'] = '图片上传失败!';
            $result['url'] = '';
            return json_encode($result,true);
        }
    }
    /**
     * 后台：wangEditor
     * @return \think\response\Json
     */
    public function editUpload(){
        // 获取上传文件表单字段名
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件
        $file = request()->file($fileKey['0']);
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->validate(['ext' => 'jpg,png,gif,jpeg'])->move('uploads');
        if($info){
            $path=str_replace('\\','/',$info->getSaveName());
            return '/uploads/'. $path;
        }else{
            // 上传失败获取错误信息
            $result['code'] =1;
            $result['msg'] = '图片上传失败!';
            $result['data'] = '';
            return json_encode($result,true);
        }
    }
    //多图上传
    public function upImages(){
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件
        $file = request()->file($fileKey['0']);
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->validate(['ext' => 'jpg,png,gif,jpeg'])->move(Env::get('root_path') . 'public/uploads');
        if($info){
            $result['code'] = 0;
            $result['msg'] = '图片上传成功!';
            $path=str_replace('\\','/',$info->getSaveName());
            $result["src"] = '/uploads/'. $path;
            return $result;
        }else{
            // 上传失败获取错误信息
            $result['code'] =1;
            $result['msg'] = '图片上传失败!';
            return $result;
        }
    }
    /**
     * 后台：NKeditor
     * @return \think\response\Json
     */
    public function editimg(){
        $allowExtesions = array(
            'image' => 'gif,jpg,jpeg,png,bmp',
            'flash' => 'swf,flv',
            'media' => 'swf,flv,mp3,wav,wma,wmv,mid,avi,mpg,asf,rm,rmvb',
            'file' => 'doc,docx,xls,xlsx,ppt,htm,html,txt,zip,rar,gz,bz2',
        );
        // 获取上传文件表单字段名
        $fileKey = array_keys(request()->file());
        // 获取表单上传文件
        $file = request()->file($fileKey['0']);
        // 移动到框架应用根目录/public/uploads/ 目录下
        $info = $file->validate(['ext'=>$allowExtesions[input('fileType')]])->move('./uploads');
        if($info){
            $path=str_replace('\\','/',$info->getSaveName());
            $url = '/uploads/'. $path;
            $result['code'] = '000';
            $result['message'] = '图片上传成功!';
            $result['item'] = ['url'=>$url];
            return json($result);
        }else{
            // 上传失败获取错误信息
            $result['code'] =001;
            $result['message'] = $file->getError();
            $result['url'] = '';
            return json($result);
        }
    }
}